﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace BabyShop.DAO
{
    public class User
    {
        public static bool isValid(DBO.User u)
        {
            string sql = "select count(*) from users where username=@username and password=@password and deleted = 0";            
            SqlConnection conn = new SqlConnection(DB_Provider.conn_str);
            SqlCommand cmd = new SqlCommand(sql,conn);
            int ret = 0;
            try
            {
                conn.Open();
                cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = u.username;
                cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = u.password;
                ret = (int)cmd.ExecuteScalar();
            }
            catch(Exception e)
            {
                Console.Write(e.Message);
            }
            finally
            {
                conn.Close();
            }            
            return ret > 0;
        }
        public static DataTable getAll()
        {
            string sql = "select * from users where deleted = 0";
            DataTable res = new DataTable();
            SqlConnection conn = new SqlConnection(DB_Provider.conn_str);
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            da.Fill(res);
            return res;
            
        }

        public static DataTable find(string name)
        {
            //name = Utils.RemoveUnicode(name, true);
            string sql = "select * from users where username like @name and deleted = 0";
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
            da.SelectCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value = "%" + name + "%";
            da.Fill(dt);
            return dt;
        }

        public static DataTable find_user(string name)
        {
            //name = Utils.RemoveUnicode(name, true);
            string sql = "select * from users where username = @name and deleted = 0";
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
            da.SelectCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
            da.Fill(dt);
            return dt;
        }
        public static DataTable find_email(string name)
        {
            //name = Utils.RemoveUnicode(name, true);
            string sql = "select * from users where email = @name and deleted = 0";
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
            da.SelectCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
            da.Fill(dt);
            return dt;
        } 
        public static void insert(DBO.User us)
        {
            string sql = "insert into users values(@username,@password,@email,@fullname,@address,@birthday,@created,@deleted)";
            SqlConnection conn = new SqlConnection(DB_Provider.conn_str);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = us.username;
            cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = us.password;
            cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = us.email;
            cmd.Parameters.Add("@fullname", SqlDbType.NVarChar).Value = us.fullname;
            cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = us.address;
            cmd.Parameters.Add("@birthday", SqlDbType.NVarChar).Value = us.birthday;
            cmd.Parameters.Add("@created", SqlDbType.DateTime).Value = us.created;
            cmd.Parameters.Add("@deleted", SqlDbType.Int).Value = 0;
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public static void Update(DBO.User us)
        {
            string sql = "update users set password=@password, email=@email, fullname=@full, address=@add, birthday=@birth where id = @id";
            SqlConnection conn = new SqlConnection(DB_Provider.conn_str);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = us.password;
            cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = us.email;
            cmd.Parameters.Add("@full", SqlDbType.NVarChar).Value = us.fullname;
            cmd.Parameters.Add("@add", SqlDbType.NVarChar).Value = us.address;
            cmd.Parameters.Add("@birth", SqlDbType.NVarChar).Value = us.birthday;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = us.ID;
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public static void Delete(int id)
        {
            string sql = "update users set deleted = 1 where id = @id";
            SqlConnection conn = new SqlConnection(DB_Provider.conn_str);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public static SqlDataAdapter getAdapter()
        {
            string sql = "select * from users where deleted = 0";
            return DB_Provider.getAdapter(sql);
        }
        public static DataTable getuser(int id)
        {
            string sql = "select * from users where id=@id";
            SqlConnection conn = new SqlConnection(DB_Provider.conn_str);
            DataTable res = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            da.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;
            da.Fill(res);
            return res;
        }

        public static DBO.User InfoLoginUser(string username, string password)
        {
            string sql = "select count(*) from users where username=@username and password=@password and deleted = 0";
            SqlConnection conn = new SqlConnection(DB_Provider.conn_str);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
            cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = password;
            DBO.User u = new DBO.User();
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                u.ID = (int)rd[0];
               // u.username = rd[1].ToString();
                //u.email = rd[3].ToString();
               // u.fullname = rd[4].ToString();
                u.fullname = "hung nguyen";
            }
            conn.Close();
            return u;
        }
    }
}